--    Program: 		CAPPOIMP_REQUISITION_LINE.sql
--    Description:  Import PO REQUISITION LINES All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_requisition_line_id	number;
   
   cursor cur_requisition_lines_all is
      select * from CRST_PO_REQUISITION_LINES_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_requisition_lines_all loop   	
     l_err_msg_tmp := '';
     
     select PO_REQUISITION_LINES_S.NEXTVAL 
     INTO l_requisition_line_id
     FROM DUAL;
     
     update CRST_PO_REQUISITION_LINES_ALL
     set    requisition_line_id_n = l_requisition_line_id
     where  requisition_line_id = cur.REQUISITION_LINE_ID;
     
     begin
       -- import data
       insert into PO_REQUISITION_LINES_ALL 
       (
            REQUISITION_LINE_ID            ,
            REQUISITION_HEADER_ID          ,
            LINE_NUM                       ,
            LINE_TYPE_ID                   ,
            CATEGORY_ID                    ,
            ITEM_DESCRIPTION               ,
            UNIT_MEAS_LOOKUP_CODE          ,
            UNIT_PRICE                     ,
            QUANTITY                       ,
            DELIVER_TO_LOCATION_ID         , 
            TO_PERSON_ID                   ,
            LAST_UPDATE_DATE               ,
            LAST_UPDATED_BY                ,
            SOURCE_TYPE_CODE               ,
            LAST_UPDATE_LOGIN              ,
            CREATION_DATE                  ,
            CREATED_BY                     ,
            ITEM_ID                        ,
            ITEM_REVISION                  ,
            QUANTITY_DELIVERED             ,
            SUGGESTED_BUYER_ID             ,
            ENCUMBERED_FLAG                ,
            RFQ_REQUIRED_FLAG              ,
            NEED_BY_DATE                   ,
            LINE_LOCATION_ID               ,
            MODIFIED_BY_AGENT_FLAG         ,
            PARENT_REQ_LINE_ID             ,
            JUSTIFICATION                  ,
            NOTE_TO_AGENT                  ,
            NOTE_TO_RECEIVER               ,
            PURCHASING_AGENT_ID            ,
            DOCUMENT_TYPE_CODE             ,
            BLANKET_PO_HEADER_ID           ,
            BLANKET_PO_LINE_NUM            ,
            CURRENCY_CODE                  ,
            RATE_TYPE                      ,
            RATE_DATE                      ,
            RATE                           ,
            CURRENCY_UNIT_PRICE            ,
            SUGGESTED_VENDOR_NAME          ,
            SUGGESTED_VENDOR_LOCATION      ,
            SUGGESTED_VENDOR_CONTACT       ,
            SUGGESTED_VENDOR_PHONE         ,
            SUGGESTED_VENDOR_PRODUCT_CODE  ,
            UN_NUMBER_ID                   ,
            HAZARD_CLASS_ID                ,
            MUST_USE_SUGG_VENDOR_FLAG      ,
            REFERENCE_NUM                  ,
            ON_RFQ_FLAG                    ,
            URGENT_FLAG                    ,
            CANCEL_FLAG                    ,
            SOURCE_ORGANIZATION_ID         ,
            SOURCE_SUBINVENTORY            ,
            DESTINATION_TYPE_CODE          ,
            DESTINATION_ORGANIZATION_ID    ,
            DESTINATION_SUBINVENTORY       ,
            QUANTITY_CANCELLED             ,
            CANCEL_DATE                    ,
            CANCEL_REASON                  ,
            CLOSED_CODE                    ,
            AGENT_RETURN_NOTE              ,
            CHANGED_AFTER_RESEARCH_FLAG    ,
            VENDOR_ID                      ,
            VENDOR_SITE_ID                 ,
            VENDOR_CONTACT_ID              ,
            RESEARCH_AGENT_ID              ,
            ON_LINE_FLAG                   ,
            WIP_ENTITY_ID                  ,
            WIP_LINE_ID                    ,
            WIP_REPETITIVE_SCHEDULE_ID     ,
            WIP_OPERATION_SEQ_NUM          ,
            WIP_RESOURCE_SEQ_NUM           ,
            ATTRIBUTE_CATEGORY             ,
            DESTINATION_CONTEXT            ,
            INVENTORY_SOURCE_CONTEXT       ,
            VENDOR_SOURCE_CONTEXT          ,
            ATTRIBUTE1                     ,
            ATTRIBUTE2                     ,
            ATTRIBUTE3                     ,
            ATTRIBUTE4                     ,
            ATTRIBUTE5                     ,
            ATTRIBUTE6                     ,
            ATTRIBUTE7                     ,
            ATTRIBUTE8                     ,
            ATTRIBUTE9                     ,
            ATTRIBUTE10                    ,
            ATTRIBUTE11                    ,
            ATTRIBUTE12                    ,
            ATTRIBUTE13                    ,
            ATTRIBUTE14                    ,
            ATTRIBUTE15                    ,
            BOM_RESOURCE_ID                ,
            REQUEST_ID                     ,
            PROGRAM_APPLICATION_ID         ,
            PROGRAM_ID                     ,
            PROGRAM_UPDATE_DATE            ,
            USSGL_TRANSACTION_CODE         ,
            GOVERNMENT_CONTEXT             ,
            CLOSED_REASON                  ,
            CLOSED_DATE                    ,
            TRANSACTION_REASON_CODE        ,
            QUANTITY_RECEIVED              ,
            SOURCE_REQ_LINE_ID             ,
            ORG_ID                         ,
            GLOBAL_ATTRIBUTE1              ,
            GLOBAL_ATTRIBUTE2              ,
            GLOBAL_ATTRIBUTE3              ,
            GLOBAL_ATTRIBUTE4              ,
            GLOBAL_ATTRIBUTE5              ,
            GLOBAL_ATTRIBUTE6              ,
            GLOBAL_ATTRIBUTE7              ,
            GLOBAL_ATTRIBUTE8              ,
            GLOBAL_ATTRIBUTE9              ,
            GLOBAL_ATTRIBUTE10             ,
            GLOBAL_ATTRIBUTE11             ,
            GLOBAL_ATTRIBUTE12             ,
            GLOBAL_ATTRIBUTE13             ,
            GLOBAL_ATTRIBUTE14             ,
            GLOBAL_ATTRIBUTE15             ,
            GLOBAL_ATTRIBUTE16             ,
            GLOBAL_ATTRIBUTE17             ,
            GLOBAL_ATTRIBUTE18             ,
            GLOBAL_ATTRIBUTE19             ,
            GLOBAL_ATTRIBUTE20             ,
            GLOBAL_ATTRIBUTE_CATEGORY      ,
            KANBAN_CARD_ID                 ,
            CATALOG_TYPE                   ,
            CATALOG_SOURCE                 ,
            MANUFACTURER_ID                ,
            MANUFACTURER_NAME              ,
            MANUFACTURER_PART_NUMBER       ,
            REQUESTER_EMAIL                ,
            REQUESTER_FAX                  ,
            REQUESTER_PHONE                ,
            UNSPSC_CODE                    ,
            OTHER_CATEGORY_CODE            ,
            SUPPLIER_DUNS                  ,
            TAX_STATUS_INDICATOR           ,
            PCARD_FLAG                     ,
            NEW_SUPPLIER_FLAG              ,
            AUTO_RECEIVE_FLAG              ,
            TAX_USER_OVERRIDE_FLAG         ,
            TAX_CODE_ID                    ,
            NOTE_TO_VENDOR                 ,
            OKE_CONTRACT_VERSION_ID        ,
            OKE_CONTRACT_HEADER_ID         ,
            ITEM_SOURCE_ID                 ,
            SUPPLIER_REF_NUMBER            ,
            SECONDARY_UNIT_OF_MEASURE      ,
            SECONDARY_QUANTITY             ,
            PREFERRED_GRADE                ,
            SECONDARY_QUANTITY_RECEIVED    ,
            SECONDARY_QUANTITY_CANCELLED   ,
            VMI_FLAG                       ,
            AUCTION_HEADER_ID              ,
            AUCTION_DISPLAY_NUMBER         ,
            AUCTION_LINE_NUMBER            ,
            REQS_IN_POOL_FLAG              ,
            BID_NUMBER                     ,
            BID_LINE_NUMBER                
       )
       values
       (
            l_requisition_line_id              ,
            CAPPOIMP_MAPPING.GetPRHeaderIDMapping(cur.REQUISITION_HEADER_ID),
            cur.LINE_NUM                       ,
            cur.LINE_TYPE_ID                   ,
            cur.CATEGORY_ID                    ,
            cur.ITEM_DESCRIPTION               ,
            cur.UNIT_MEAS_LOOKUP_CODE          ,
            cur.UNIT_PRICE                     ,
            cur.QUANTITY                       ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.DELIVER_TO_LOCATION_ID),
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.TO_PERSON_ID),
            cur.LAST_UPDATE_DATE               ,
            CUR.LAST_UPDATED_BY,
            cur.SOURCE_TYPE_CODE               ,
            cur.LAST_UPDATE_LOGIN              ,
            cur.CREATION_DATE                  ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            --CAPPOIMP_MAPPING.GetItemIDMapping(cur.DESTINATION_ORGANIZATION_ID, cur.ITEM_ID),
            CAPPOIMP_MAPPING.GetItemIDMapping(cur.ITEM_ID),
            cur.ITEM_REVISION                  ,
            cur.QUANTITY_DELIVERED             ,
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.SUGGESTED_BUYER_ID),
            cur.ENCUMBERED_FLAG                ,
            cur.RFQ_REQUIRED_FLAG              ,
            cur.NEED_BY_DATE                   ,
            CAPPOIMP_MAPPING.GetPOLineLocationIDMapping(cur.LINE_LOCATION_ID),
            cur.MODIFIED_BY_AGENT_FLAG         ,
            cur.PARENT_REQ_LINE_ID             , --NULL
            cur.JUSTIFICATION                  ,
            cur.NOTE_TO_AGENT                  ,
            cur.NOTE_TO_RECEIVER               ,
            cur.PURCHASING_AGENT_ID            , --NULL
            cur.DOCUMENT_TYPE_CODE             ,
            cur.BLANKET_PO_HEADER_ID           , --NULL
            cur.BLANKET_PO_LINE_NUM            , --NULL
            cur.CURRENCY_CODE                  ,
            cur.RATE_TYPE                      ,
            cur.RATE_DATE                      ,
            cur.RATE                           ,
            cur.CURRENCY_UNIT_PRICE            ,
            cur.SUGGESTED_VENDOR_NAME          ,
            cur.SUGGESTED_VENDOR_LOCATION      ,
            cur.SUGGESTED_VENDOR_CONTACT       ,
            cur.SUGGESTED_VENDOR_PHONE         ,
            cur.SUGGESTED_VENDOR_PRODUCT_CODE  ,
            cur.UN_NUMBER_ID                   ,
            cur.HAZARD_CLASS_ID                ,
            cur.MUST_USE_SUGG_VENDOR_FLAG      ,
            cur.REFERENCE_NUM                  ,
            cur.ON_RFQ_FLAG                    ,
            cur.URGENT_FLAG                    ,
            cur.CANCEL_FLAG                    ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.SOURCE_ORGANIZATION_ID),
            cur.SOURCE_SUBINVENTORY            ,
            cur.DESTINATION_TYPE_CODE          ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.DESTINATION_ORGANIZATION_ID),
            cur.DESTINATION_SUBINVENTORY       ,
            cur.QUANTITY_CANCELLED             ,
            cur.CANCEL_DATE                    ,
            cur.CANCEL_REASON                  ,
            cur.CLOSED_CODE                    ,
            cur.AGENT_RETURN_NOTE              ,
            cur.CHANGED_AFTER_RESEARCH_FLAG    ,
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            CAPPOIMP_MAPPING.GetVendorContactIDMapping(cur.VENDOR_CONTACT_ID),
            cur.RESEARCH_AGENT_ID              ,
            cur.ON_LINE_FLAG                   ,
            cur.WIP_ENTITY_ID                  ,
            cur.WIP_LINE_ID                    ,
            cur.WIP_REPETITIVE_SCHEDULE_ID     ,
            cur.WIP_OPERATION_SEQ_NUM          ,
            cur.WIP_RESOURCE_SEQ_NUM           ,
            cur.ATTRIBUTE_CATEGORY             ,
            cur.DESTINATION_CONTEXT            ,
            cur.INVENTORY_SOURCE_CONTEXT       ,
            cur.VENDOR_SOURCE_CONTEXT          ,
            cur.ATTRIBUTE1                     ,
            cur.ATTRIBUTE2                     ,
            cur.ATTRIBUTE3                     ,
            cur.ATTRIBUTE4                     ,
            cur.ATTRIBUTE5                     ,
            cur.ATTRIBUTE6                     ,
            cur.ATTRIBUTE7                     ,
            cur.ATTRIBUTE8                     ,
            cur.ATTRIBUTE9                     ,
            cur.ATTRIBUTE10                    ,
            cur.ATTRIBUTE11                    ,
            cur.ATTRIBUTE12                    ,
            cur.ATTRIBUTE13                    ,
            cur.ATTRIBUTE14                    ,
            cur.ATTRIBUTE15                    ,
            cur.BOM_RESOURCE_ID                ,
            cur.REQUEST_ID                     ,
            cur.PROGRAM_APPLICATION_ID         ,
            cur.PROGRAM_ID                     ,
            cur.PROGRAM_UPDATE_DATE            ,
            cur.USSGL_TRANSACTION_CODE         ,
            cur.GOVERNMENT_CONTEXT             ,
            cur.CLOSED_REASON                  ,
            cur.CLOSED_DATE                    ,
            cur.TRANSACTION_REASON_CODE        ,
            cur.QUANTITY_RECEIVED              ,
            cur.SOURCE_REQ_LINE_ID             ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.GLOBAL_ATTRIBUTE1              ,
            cur.GLOBAL_ATTRIBUTE2              ,
            cur.GLOBAL_ATTRIBUTE3              ,
            cur.GLOBAL_ATTRIBUTE4              ,
            cur.GLOBAL_ATTRIBUTE5              ,
            cur.GLOBAL_ATTRIBUTE6              ,
            cur.GLOBAL_ATTRIBUTE7              ,
            cur.GLOBAL_ATTRIBUTE8              ,
            cur.GLOBAL_ATTRIBUTE9              ,
            cur.GLOBAL_ATTRIBUTE10             ,
            cur.GLOBAL_ATTRIBUTE11             ,
            cur.GLOBAL_ATTRIBUTE12             ,
            cur.GLOBAL_ATTRIBUTE13             ,
            cur.GLOBAL_ATTRIBUTE14             ,
            cur.GLOBAL_ATTRIBUTE15             ,
            cur.GLOBAL_ATTRIBUTE16             ,
            cur.GLOBAL_ATTRIBUTE17             ,
            cur.GLOBAL_ATTRIBUTE18             ,
            cur.GLOBAL_ATTRIBUTE19             ,
            cur.GLOBAL_ATTRIBUTE20             ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY      ,
            cur.KANBAN_CARD_ID                 ,
            cur.CATALOG_TYPE                   ,
            cur.CATALOG_SOURCE                 ,
            cur.MANUFACTURER_ID                ,
            cur.MANUFACTURER_NAME              ,
            cur.MANUFACTURER_PART_NUMBER       ,
            cur.REQUESTER_EMAIL                ,
            cur.REQUESTER_FAX                  ,
            cur.REQUESTER_PHONE                ,
            cur.UNSPSC_CODE                    ,
            cur.OTHER_CATEGORY_CODE            ,
            cur.SUPPLIER_DUNS                  ,
            cur.TAX_STATUS_INDICATOR           ,
            cur.PCARD_FLAG                     ,
            cur.NEW_SUPPLIER_FLAG              ,
            cur.AUTO_RECEIVE_FLAG              ,
            cur.TAX_USER_OVERRIDE_FLAG         ,
            cur.TAX_CODE_ID                    ,
            cur.NOTE_TO_VENDOR                 ,
            cur.OKE_CONTRACT_VERSION_ID        ,
            cur.OKE_CONTRACT_HEADER_ID         ,
            cur.ITEM_SOURCE_ID                 ,
            cur.SUPPLIER_REF_NUMBER            ,
            cur.SECONDARY_UNIT_OF_MEASURE      ,
            cur.SECONDARY_QUANTITY             ,
            cur.PREFERRED_GRADE                ,
            cur.SECONDARY_QUANTITY_RECEIVED    ,
            cur.SECONDARY_QUANTITY_CANCELLED   ,
            cur.VMI_FLAG                       ,
            cur.AUCTION_HEADER_ID              ,
            cur.AUCTION_DISPLAY_NUMBER         ,
            cur.AUCTION_LINE_NUMBER            ,
            cur.REQS_IN_POOL_FLAG              ,
            cur.BID_NUMBER                     ,
            cur.BID_LINE_NUMBER                
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_REQUISITION_LINE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
